﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.UI.WebControls;

namespace simpleHotel.Business
{
    public class HotelBLL
    {

        ////public string GetMapIdRoomTypeOfHotel(int hotelid, int roomtypeid)
        ////{
        ////    var model = Singleton.NewDataContext();

        ////    var query = from a in model.MapHotelRoomCategories
        ////                where a.HotelId == hotelid && a.RoomCategoryId == roomtypeid
        ////                select a;

        ////    return query.Single().Id.ToString();
        ////}


        public DataTable GetHotelRoomPrice(int hotelid, int roomtypeid)
        {
            var model = Singleton.NewDataContext();
            var query = from r in model.MapHotelRoomCategories
                        where r.HotelId == hotelid &&
                              r.RoomCategoryId == roomtypeid
                        select r;
           
            SqlCommand cmd = (SqlCommand)model.GetCommand(query);
            DataTable tb = new DataTable();
            new SqlDataAdapter(cmd).Fill(tb);

            return tb;
        }

        public void GetHotelImages(int hotelid, int roomtypeid, Literal lLarge, Literal lSmall)
        {
            var model = Singleton.NewDataContext();

            IQueryable<Hotel> query1 = null;
            IQueryable<MapHotelRoomCategory> query2 = null;
            string images = string.Empty;
            string path = string.Empty;

            if (roomtypeid == 0)
            {
                query1 = from h in model.Hotels
                         where h.ID == hotelid
                         select h;
            }
            else
            {
                query2 = from a in model.MapHotelRoomCategories
                         where a.HotelId == hotelid && a.RoomCategoryId == roomtypeid
                         select a;
            }




            if (roomtypeid == 0)
            {
                if (query1.Single().Images != null)
                    images = query1.Single().Images;
            }
            else
            {
                if(query2.Single().Images!=null)
                    images = query2.Single().Images;
            }

                string[] arr = images.Split(';');

                if (roomtypeid == 0)
                    path = "ImageData/" + hotelid.ToString() + "/Hotel/";
                else
                    path = "ImageData/" + hotelid.ToString() + "/R" + query2.Single().Id + "/";

                string LargeImgHtml = string.Empty;
                string ThumbImgHtml = string.Empty;
                string prefixLarge = "large_";
                string prefixSmall = "small_";

                for (int i = 0; i < arr.Length; i++)
                {
                    LargeImgHtml += "<img src=\"" + path + prefixLarge + arr[i] + "\" alt=\"\" />\n";

                    if (i == 0)
                        ThumbImgHtml += "<li class='active' rel='1'><img src=\"" + path + prefixSmall + arr[i] + "\" alt=\"\" /></li>\n";
                    else
                        ThumbImgHtml += "<li rel='" + (i + 1).ToString() + "'><img src=\"" + path + prefixSmall + arr[i] + "\" alt=\"\" /></li>\n";
                }

                lLarge.Text = LargeImgHtml;
                lSmall.Text = ThumbImgHtml;
            }
        

        public DataTable HotelSearch(string hotelname,int provinceid,string sortrange,string sortprice)
        {
            var model = Singleton.NewDataContext();
            List<String> hotelfilter = new List<String>();


            string conditions=string.Empty;

            var query = from h in model.Hotels
                        ////join a in model.MapHotelRoomAvailables on h.ID equals a.HotelId
                        join r in model.RangeHotels on h.RangeID equals r.ID
                        select new {h, r};


            if (hotelname != null)
            {
                query = query.Where(h => h.h.HotelName.Contains(hotelname));
            }

            if (!string.IsNullOrEmpty(provinceid.ToString()))
            {
                query = query.Where(h => h.h.ProvinceID.Equals(provinceid));
            }

            if (sortrange != "0")
            {
                query = query.Where(h=>h.h.RangeID.Equals(sortrange));
            }

            if (HttpContext.Current.Session["CHECKINDATE"] != null && HttpContext.Current.Session["CHECKOUTDATE"] != null)
            {
                ////DateTime CheckInDate = DateTime.Parse((HttpContext.Current.Session["CHECKINDATE"]).ToString());
                ////DateTime CheckOutDate = DateTime.Parse((HttpContext.Current.Session["CHECKOUTDATE"]).ToString());

                string fromdate=HttpContext.Current.Session["CHECKINDATE"].ToString();
                string todate=HttpContext.Current.Session["CHECKOUTDATE"].ToString();

                fromdate = ReplaceDate(fromdate);
                todate = ReplaceDate(todate);

                DateTime CheckInDate = Convert.ToDateTime(fromdate);
                DateTime CheckOutDate = Convert.ToDateTime(todate);

                foreach (var item in query)
                {
                    var sql = from r in model.MapHotelRoomAvailables
                              where (r.HotelId == item.h.ID && r.FromDate <= CheckInDate && r.ToDate >= CheckInDate &&
                                     r.FromDate <= CheckOutDate && r.ToDate >= CheckOutDate)
                              select r;

                    if (sql.Count() > 0)
                             hotelfilter.Add(sql.FirstOrDefault().HotelId.ToString());
                }

                    query = from h in query
                            where hotelfilter.Contains(h.h.ID.ToString())
                            select h;
            }
            
            if (sortprice != "0")
            {
                if(sortprice=="1")
                    query = query.OrderBy(h => h.h.Prices).ThenByDescending(h=>h.h.RangeID);
                else
                    query = query.OrderByDescending(h => h.h.Prices);
            }

            SqlCommand cmd = (SqlCommand)model.GetCommand(query);
            DataTable tb = new DataTable();
            new SqlDataAdapter(cmd).Fill(tb);


            return tb;
        }


        public string ReplaceDate(string input)
        {
            string[] arr = input.Split('/');
            string res = string.Empty;
            if (arr.Length > 0)
            {
                res = arr[2] + "/" + arr[1] + "/" + arr[0];
            }

            return res;
        }

        public DataTable HotelRange()
        {
            var model = Singleton.NewDataContext();
            ////model.Connection.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["SimpleHotel_ConStr"].ToString();

            var query = from h in model.RangeHotels
                        select h;

            SqlCommand cmd = (SqlCommand)model.GetCommand(query);
            DataTable tb = new DataTable();
            new SqlDataAdapter(cmd).Fill(tb);

            return tb;
        }


        public string GetMinMaxRoomPrice(int hotelid)
        {
            string res = string.Empty;
            List<double> arr = new List<double>();

            DataTable tb = GetRoomTypeByHotelID(hotelid);
            if(tb!=null)
                if (tb.Rows.Count > 0)
                {
                    foreach (DataRow dr in tb.Rows)
                    {
                        arr.Add(Convert.ToDouble(dr["PricePerDay"]+""));
                    }
                }

            if (arr.Count > 0)
            {
                res += string.Format("{0:#,###}",Min(arr)).Replace(",",".") + " VND<br /><br />";
                res += string.Format("{0:#,###}", Max(arr)).Replace(",", ".") + " VND";
            }

            return res;
        }


        private double Min(List<double> numbers)
        {
            double m = numbers[0];

            for (int i = 0; i < numbers.Count; i++)
                if (m > numbers[i])
                    m = numbers[i];

            return m;
        }

        private double Max(List<double> numbers)
        {
            double m = numbers[0];

            for (int i = 0; i < numbers.Count; i++)
                if (m < numbers[i])
                    m = numbers[i];

            return m;
        }

        public DataTable GetRoomTypeByHotelID(int hotelid)
        {
            var model = Singleton.NewDataContext();
            ////model.Connection.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["SimpleHotel_ConStr"].ToString();

            var query = from type in model.MapHotelRoomCategories
                        join r in model.RoomCategories on type.RoomCategoryId equals r.ID
                        where type.HotelId==hotelid
                        select new {type,r};

            SqlCommand cmd = (SqlCommand)model.GetCommand(query);
            DataTable tb = new DataTable();
            new SqlDataAdapter(cmd).Fill(tb);

            return tb;
        }

        public bool CheckRoomAvailable(int hotelid,int roomtypeid)
        {
            bool res = false;

            if (HttpContext.Current.Session["CHECKINDATE"] != null && HttpContext.Current.Session["CHECKOUTDATE"] != null)
            {
                DateTime CheckInDate = Convert.ToDateTime(ReplaceDate(HttpContext.Current.Session["CHECKINDATE"]+""));
                DateTime CheckOutDate = Convert.ToDateTime(ReplaceDate(HttpContext.Current.Session["CHECKOUTDATE"]+""));

                var model = Singleton.NewDataContext();
               

                var query = from r in model.MapHotelRoomAvailables
                            where r.HotelId == hotelid && r.RoomCategoryId==roomtypeid &&
                                  r.FromDate <= CheckInDate && r.ToDate >= CheckInDate &&
                                  r.FromDate <= CheckOutDate && r.ToDate >= CheckOutDate
                            select r;

                if (query.Count() > 0)
                    res = true;
            }

            return res;
        }

        public DataTable GetProvinces()
        {
            var model = Singleton.NewDataContext();
           //model.Connection.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["SimpleHotel_ConStr"].ToString();

            var query = from p in model.Provinces
                        select p;

            SqlCommand cmd = (SqlCommand)model.GetCommand(query);
            DataTable tb = new DataTable();
            new SqlDataAdapter(cmd).Fill(tb);

            return tb;
        }

        public Hotel GetHotelByID(int hotelid)
        {
            var model = Singleton.NewDataContext();
            ////model.Connection.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["SimpleHotel_ConStr"].ToString();

            var query = from h in model.Hotels
                        where h.ID==hotelid
                        select h;

            return query.First(); ////query.Single().Address1.ToString();
        }

        ////public DataTable FindHotels(string provinceid)
        ////{
        ////    var model = Singleton.DataContext;
        ////    model.Connection.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["SimpleHotel_ConStr"].ToString();

        ////    var query = from p in model.Provinces
        ////                select p;

        ////    SqlCommand cmd = (SqlCommand)model.GetCommand(query);
        ////    DataTable tb = new DataTable();
        ////    new SqlDataAdapter(cmd).Fill(tb);

        ////    return tb;
        ////}

    }
}
